package dao;

import beans.TablaTo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.HashMap;

import utildb.GenericDAO;
import utildb.Transaction;
import utildb.TransactionException;
import utildb.Util;


public class TablaDAO extends GenericDAO  {
    static HashMap tablas = null;
    
    public TablaDAO(Transaction transacction) {
        super(transacction);
    }

    public void create(TablaTo to) throws TransactionException {
		try {
			int i = 1;
			Connection c;
			PreparedStatement statement = null;
			c = (Connection) transaction.getConnection();

			statement = c.prepareStatement("insert into "
                                        + " mysql.vemc_tabl "
					+ " (ccod_tipo, cnom_tabl, cnom_tabl_ingl, "
					+ " ccod_refe, nest_tabl)"
					+ "values (?, ?, ?, ?, ?)");

			statement.setString(i++, to.getCcod_Tipo());
			statement.setString(i++, to.getCnom_Tabl());
			statement.setString(i++, to.getCnom_Tabl_Ingl());
                        statement.setString(i++, to.getCcod_Refe());
                        statement.setString(i++, to.getNest_Tabl().toString());

			int n = statement.executeUpdate();
			statement.close();
			if (n == 0)
				throw new TransactionException("No se pudo crear el tipo de tabla");
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
	}

    public void eliminar(String ccodigo) throws TransactionException {
        try {
            int i = 1;
            Connection c;
            PreparedStatement statement = null;
            String ccodigoValida = Util.ignoraSQLInjection(ccodigo);
            c = (Connection) transaction.getConnection();
            statement = c.prepareStatement("delete from mysql.vemc_tabl " +
                                               " where ccod_tipo = ? ");

            statement.setString(i++, ccodigoValida );

            int n;
            n = statement.executeUpdate();
            statement.close();
            if (n == 0)
                        throw new TransactionException("No se pudo eliminar el tipo de tabla");
        } catch (SQLException e1) {
                throw new TransactionException(e1);
        }
    }

	public ArrayList arrayTablas(String codigo) throws TransactionException {
		String lc_SQL;
                Connection c;
		Statement statement = null;
		ArrayList all = null;
		try {
			c = transaction.getConnection();
			statement = c.createStatement();
                        lc_SQL = "select * from mysql.vemc_tabl " +
                                 "where ccod_tipo like '" + codigo + "' " +
                                  " And nEst_Tabl = 1 ";
			ResultSet rs = statement.executeQuery(lc_SQL);
			all = new ArrayList();
			while (rs.next()) {
                                      String ccod_tipo = rs.getString("ccod_tipo");
                                      String cnom_tabl = rs.getString("cnom_tabl");
                                      String cnom_tabl_ingl = rs.getString("cnom_tabl_ingl");
                                      String ccod_refe = rs.getString("ccod_refe");
                                      int nest_tabl = rs.getInt("nest_tabl");
           			      all.add(new TablaTo( ccod_tipo, cnom_tabl,
                                            cnom_tabl_ingl, ccod_refe, nest_tabl ));
			}
			rs.close();
			statement.close();
		} catch (SQLException e1) {
			throw new TransactionException(e1);
		}
		return all;
	}

    public HashMap hashMapTablas() throws TransactionException {
		Connection c;
		Statement statement = null;
			tablas = new HashMap();
			try {
				c = transaction.getConnection();
				statement = c.createStatement();
				ResultSet rs = statement.executeQuery(
                                        "select * from mysql.vemc_tabl");
				while (rs.next()) {
                                      String ccod_tipo = rs.getString("ccod_tipo");
                                      String cnom_tabl = rs.getString("cnom_tabl");
                                      String cnom_tabl_ingl = rs.getString("cnom_tabl_ingl");
                                      String ccod_refe = rs.getString("ccod_refe");
                                      int nest_tabl = rs.getInt("nest_tabl");
                                    tablas.put(ccod_tipo, new TablaTo(ccod_tipo, cnom_tabl,
                                            cnom_tabl_ingl, ccod_refe, nest_tabl ));
				}
				rs.close();
				statement.close();
			} catch (SQLException e1) {
				throw new TransactionException(e1);
			}
		return tablas;
	}
}
